#! /bin/bash
PRESTO_HOME=/opt/cloudera/parcels/presto/bin/presto
if [[ $1 == '' ]];
then dt=`date -d '-1 day' "+%Y-%m-%d"`
else dt=$1
fi

/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute"
insert into hive.zx_rpt.rpt_attendance
select  class_date,
        class_id,
        morning_attendance_num,
        cast(round(cast(morning_attendance_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as morning_attendance_rate,
        morning_later_num,
        cast(round(cast(morning_later_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as morning_later_rate,
        morning_leave_num,
        cast(round(cast(morning_leave_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as morning_leave_rate,
        morning_truant_num,
        cast(round(cast(morning_truant_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as morning_truant_rate,
        afternoon_attendance_num,
        cast(round(cast(afternoon_attendance_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as afternoon_attendance_rate,
        afternoon_later_num,
        cast(round(cast(afternoon_later_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as afternoon_later_rate,
        afternoon_leave_num,
        cast(round(cast(afternoon_leave_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as afternoon_leave_rate,
        afternoon_truant_num,
        cast(round(cast(afternoon_truant_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as afternoon_truant_rate,
        evening_attendance_num,
        cast(round(cast(evening_attendance_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as evening_attendance_rate,
        evening_later_num,
        cast(round(cast(evening_later_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as evening_later_rate,
        evening_leave_num,
        cast(round(cast(evening_leave_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as evening_leave_rate,
        evening_truant_num,
        cast(round(cast(evening_truant_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as evening_truant_rate,

       class_date as dt
from hive.zx_dws.dws_attendance
where class_date = '${dt}';
"

/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute"
insert into hive.zx_rpt.rpt_daily_attendance
select  class_date,
        class_id,
        morning_attendance_num,
        cast(round(cast(morning_attendance_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as morning_attendance_rate,
        afternoon_attendance_num,
        cast(round(cast(afternoon_attendance_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as afternoon_attendance_rate,
        evening_attendance_num,
        cast(round(cast(evening_attendance_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as evening_attendance_rate,
        class_date as dt
from hive.zx_dws.dws_attendance
where class_date = '${dt}';
"
/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute"
insert into hive.zx_rpt.rpt_daily_later
select  class_date,
        class_id,
        morning_later_num,
        cast(round(cast(morning_later_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as morning_later_rate,
        afternoon_later_num,
        cast(round(cast(afternoon_later_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as afternoon_later_rate,
        evening_later_num,
        cast(round(cast(evening_later_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as evening_later_rate,
        class_date as dt
from hive.zx_dws.dws_attendance
where class_date = '${dt}';
"

/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute"
insert into hive.zx_rpt.rpt_daily_leave
select  class_date,
        class_id,
        morning_leave_num,
        cast(round(cast(morning_leave_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as morning_leave_rate,
        afternoon_leave_num,
        cast(round(cast(afternoon_leave_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as afternoon_leave_rate,
        evening_leave_num,
        cast(round(cast(evening_leave_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as evening_leave_rate,
        class_date as dt
from hive.zx_dws.dws_attendance
where class_date = '${dt}';
"

/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute"
insert into hive.zx_rpt.rpt_daily_truant
select  class_date,
        class_id,
        morning_truant_num,
        cast(round(cast(morning_truant_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as morning_truant_rate,
        afternoon_truant_num,
        cast(round(cast(afternoon_truant_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as afternoon_truant_rate,
        evening_truant_num,
        cast(round(cast(evening_truant_num as decimal(33,2)) / cast(studying_student_count as decimal(33,2)),2) as decimal(5,2)) as evening_truant_rate,
        class_date as dt
from hive.zx_dws.dws_attendance
where class_date = '${dt}';
"
